const {Router} = require('express');
const mysql = require('mysql');
const router = Router();

/* 连接池形式操作数据库(推荐写法) */
// 1.配置连接池
var pool  = mysql.createPool({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    port: 3306,
    database: 'test',
    // 是否存在多个连接池
    multipleStatements: true
});

// 一、查询所有数据
router.get('/',(req,res)=>{
    // 2.连接池执行sql语句
    const sql = 'select * from userlist';
    pool.query(sql,(err,result,fields)=>{
        if(err){
            res.send({
                code:0,
                status:'fail',
                data:err
            })
        }else{
            res.send({
                code:1,
                status:'success',
                data:result
            })
        }
    })
})

// 二、插入数据
router.post('/',(req,res)=>{
    // 获取参数
    let {username,phone} = req.body;
    // 执行sql语句
    const sql = `insert into userlist (username,phone) values ('${username}','${phone}');`
    pool.query(sql,(err,result,fields)=>{
        if(err){
            res.send({
                code:0,
                status:'fail',
                data:err
            })
        }else{
            res.send({
                code:1,
                status:'success',
                data:result
            })
        }
    })
})

// 三、修改数据
router.patch('/',(req,res)=>{
    let {username,phone} = req.body;
    const sql = `update userlist set phone='${phone}' where username='${username}'`;
    pool.query(sql,(err,result,field)=>{
        if(err){
            res.send({
                code:0,
                status:'fail',
                data:err
            })
        }else{
            res.send({
                code:1,
                status:'success',
                data:result
            })
        }
    })
})

// 四、删除数据
router.delete('/:id',(req,res)=>{
    let {id} = req.params;
    const sql = `DELETE FROM userlist where id=${id}`;
    pool.query(sql,(err,result,fields)=>{
        if(err){
            res.send({
                code:0,
                status:'fail',
                data:err
            })
        }else{
            res.send({
                code:1,
                status:'success',
                data:result
            })
        }
    })
})


module.exports = router;



